BEGIN;
CREATE TABLE "domains_customer" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(128) NOT NULL,
    "address" varchar(128) NOT NULL,
    "address2" varchar(128) NOT NULL,
    "city" varchar(128) NOT NULL,
    "state" varchar(128) NOT NULL,
    "zip" varchar(128) NOT NULL,
    "country" varchar(2) NOT NULL,
    "phone" varchar(32) NOT NULL,
    "fax" varchar(32) NOT NULL,
    "email" varchar(128) NOT NULL,
    "emailComment" varchar(128) NOT NULL,
    "status" varchar(1) NOT NULL,
    "reseller_id" integer NOT NULL
)
;
CREATE TABLE "domains_datauser" (
    "id" integer NOT NULL PRIMARY KEY,
    "database_id" integer NOT NULL,
    "username" varchar(32) NOT NULL,
    "password" varchar(32) NOT NULL,
    "passwordClear" varchar(32) NOT NULL,
    "privAll" bool NOT NULL,
    "privSelect" bool NOT NULL,
    "privAlter" bool NOT NULL,
    "privAlterRoutine" bool NOT NULL,
    "privCreate" bool NOT NULL,
    "privCreateRoutine" bool NOT NULL,
    "privCreateTemp" bool NOT NULL,
    "privCreateUser" bool NOT NULL,
    "privCreateView" bool NOT NULL,
    "privDelete" bool NOT NULL,
    "privDrop" bool NOT NULL,
    "privExecute" bool NOT NULL,
    "privFile" bool NOT NULL,
    "privIndex" bool NOT NULL,
    "privInsert" bool NOT NULL,
    "privLockTables" bool NOT NULL,
    "privProcess" bool NOT NULL,
    "privReferences" bool NOT NULL,
    "privReload" bool NOT NULL,
    "privRelicationClient" bool NOT NULL,
    "privRelicationSlave" bool NOT NULL,
    "privShowDatabases" bool NOT NULL,
    "privShowView" bool NOT NULL,
    "privShutdown" bool NOT NULL,
    "privSuper" bool NOT NULL,
    "privUpdate" bool NOT NULL,
    "privUsage" bool NOT NULL,
    "privGrantOption" bool NOT NULL
)
;
CREATE TABLE "domains_webuser" (
    "id" integer NOT NULL PRIMARY KEY,
    "site_id" integer NOT NULL,
    "username" varchar(32) NOT NULL,
    "password" varchar(64) NOT NULL,
    "passwordClear" varchar(64) NOT NULL,
    "homeDir" varchar(256) NOT NULL,
    "shell" varchar(256) NOT NULL,
    "nameFirst" varchar(256) NOT NULL,
    "nameLast" varchar(256) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_dnssoa" (
    "id" integer NOT NULL PRIMARY KEY,
    "zone_id" integer NOT NULL,
    "serial" integer NOT NULL,
    "ttl" integer NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_reseller" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(128) NOT NULL,
    "address" varchar(128) NOT NULL,
    "address2" varchar(128) NOT NULL,
    "city" varchar(128) NOT NULL,
    "state" varchar(128) NOT NULL,
    "zip" varchar(128) NOT NULL,
    "country" varchar(2) NOT NULL,
    "phone" varchar(32) NOT NULL,
    "fax" varchar(32) NOT NULL,
    "email" varchar(128) NOT NULL,
    "emailComment" varchar(128) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_database" (
    "id" integer NOT NULL PRIMARY KEY,
    "customer_id" integer NOT NULL REFERENCES "domains_customer" ("id"),
    "name" varchar(64) NOT NULL,
    "type" varchar(64) NOT NULL
)
;
CREATE TABLE "domains_dnszone" (
    "id" integer NOT NULL PRIMARY KEY,
    "dnsID_id" integer NOT NULL,
    "recordType_id" integer NOT NULL,
    "name" varchar(128) NOT NULL,
    "value" varchar(128) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_dnstype" (
    "id" integer NOT NULL PRIMARY KEY,
    "recType" integer NOT NULL,
    "recDesc" varchar(4) NOT NULL,
    "recComment" varchar(256) NOT NULL
)
;
CREATE TABLE "domains_site" (
    "id" integer NOT NULL PRIMARY KEY,
    "customer_id" integer NOT NULL REFERENCES "domains_customer" ("id"),
    "instance_id" integer NOT NULL,
    "url" varchar(256) NOT NULL,
    "httpSite" varchar(1) NOT NULL,
    "httpsSite" varchar(1) NOT NULL,
    "httpDocRoot" varchar(256) NOT NULL,
    "httpsDocRoot" varchar(256) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_httpdevent" (
    "id" integer NOT NULL PRIMARY KEY,
    "instance_id" integer NOT NULL,
    "when" datetime NOT NULL,
    "user_id" integer NOT NULL REFERENCES "domains_webuser" ("id"),
    "comment" varchar(255) NOT NULL,
    "action" varchar(255) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_server" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(128) NOT NULL,
    "ip" varchar(15) NOT NULL,
    "comment" varchar(255) NOT NULL
)
;
CREATE TABLE "domains_contact" (
    "id" integer NOT NULL PRIMARY KEY,
    "customer_id" integer NOT NULL REFERENCES "domains_customer" ("id"),
    "nameFirst" varchar(64) NOT NULL,
    "nameLast" varchar(64) NOT NULL,
    "phoneWork" varchar(32) NOT NULL,
    "phoneWork2" varchar(32) NOT NULL,
    "phoneFax" varchar(32) NOT NULL,
    "phoneCell" varchar(32) NOT NULL,
    "email" varchar(128) NOT NULL,
    "emailComment" varchar(128) NOT NULL,
    "email2" varchar(128) NOT NULL,
    "email2Comment" varchar(128) NOT NULL,
    "afterHours" varchar(128) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_mailuser" (
    "id" integer NOT NULL PRIMARY KEY,
    "domain_id" integer NOT NULL,
    "nameFirst" varchar(64) NOT NULL,
    "nameLast" varchar(64) NOT NULL,
    "username" varchar(64) NOT NULL,
    "password" varchar(64) NOT NULL,
    "passwordClear" varchar(64) NOT NULL,
    "mailbox" varchar(255) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_ssldata" (
    "id" integer NOT NULL PRIMARY KEY,
    "site_id" integer NOT NULL REFERENCES "domains_site" ("id"),
    "cert" varchar(256) NOT NULL,
    "csr" varchar(256) NOT NULL,
    "key" varchar(256) NOT NULL,
    "passwd" varchar(256) NOT NULL,
    "cafile" varchar(256) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_dns" (
    "id" integer NOT NULL PRIMARY KEY,
    "domain_id" integer NOT NULL REFERENCES "domains_customer" ("id"),
    "name" varchar(256) NOT NULL,
    "zonefile" varchar(256) NOT NULL
)
;
CREATE TABLE "domains_apache" (
    "id" integer NOT NULL PRIMARY KEY,
    "customer_id" integer NOT NULL REFERENCES "domains_customer" ("id"),
    "configFile" varchar(256) NOT NULL,
    "socketFile" varchar(256) NOT NULL,
    "whenStarted" datetime NOT NULL,
    "whenStopped" datetime NOT NULL
)
;
CREATE TABLE "domains_ftpuser" (
    "id" integer NOT NULL PRIMARY KEY,
    "site_id" integer NOT NULL REFERENCES "domains_site" ("id"),
    "username" varchar(32) NOT NULL,
    "password" varchar(64) NOT NULL,
    "passwordClear" varchar(64) NOT NULL,
    "homeDir" varchar(256) NOT NULL,
    "nameFirst" varchar(256) NOT NULL,
    "nameLast" varchar(256) NOT NULL,
    "status" varchar(1) NOT NULL
)
;
CREATE TABLE "domains_service" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(50) NOT NULL,
    "ip_port" integer NOT NULL,
    "udp_port" integer NOT NULL,
    "pid" integer NOT NULL,
    "pidFile" varchar(256) NOT NULL
)
;
CREATE INDEX "domains_customer_reseller_id" ON "domains_customer" ("reseller_id");
CREATE INDEX "domains_datauser_database_id" ON "domains_datauser" ("database_id");
CREATE INDEX "domains_webuser_site_id" ON "domains_webuser" ("site_id");
CREATE INDEX "domains_dnssoa_zone_id" ON "domains_dnssoa" ("zone_id");
CREATE INDEX "domains_database_customer_id" ON "domains_database" ("customer_id");
CREATE INDEX "domains_dnszone_dnsID_id" ON "domains_dnszone" ("dnsID_id");
CREATE INDEX "domains_dnszone_recordType_id" ON "domains_dnszone" ("recordType_id");
CREATE INDEX "domains_site_customer_id" ON "domains_site" ("customer_id");
CREATE INDEX "domains_site_instance_id" ON "domains_site" ("instance_id");
CREATE INDEX "domains_httpdevent_instance_id" ON "domains_httpdevent" ("instance_id");
CREATE INDEX "domains_httpdevent_user_id" ON "domains_httpdevent" ("user_id");
CREATE INDEX "domains_contact_customer_id" ON "domains_contact" ("customer_id");
CREATE INDEX "domains_mailuser_domain_id" ON "domains_mailuser" ("domain_id");
CREATE INDEX "domains_ssldata_site_id" ON "domains_ssldata" ("site_id");
CREATE INDEX "domains_dns_domain_id" ON "domains_dns" ("domain_id");
CREATE INDEX "domains_apache_customer_id" ON "domains_apache" ("customer_id");
CREATE INDEX "domains_ftpuser_site_id" ON "domains_ftpuser" ("site_id");
COMMIT;
